﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using SQLiteClient;

namespace SQLHw2.appDatabase
{
    public class DatabaseFunctions
    {
        SQLiteConnection db = null;
        private string filename = "sqlhw2.DB";
        public int insert_into_tblSeating(string id, string location, int size)
        {
            if (db != null)
            {
                int rec;
                SQLiteCommand cmd = db.CreateCommand("");
                tblSeating table = new tblSeating("T1", "UP", 4);
                cmd.CommandText = "Insert into tblSEATING (TableID,TableLocation,TableSize) values (@id,@location,@size)";
                rec = cmd.ExecuteNonQuery(table);
                return rec;
            }
            else return 0;

        }
        public void create_tables()
        {
            try
            {
                db = new SQLiteConnection(filename);
                db.Open();
                
                SQLiteCommand ejobcmd = db.CreateCommand(@"CREATE TABLE tblEMPJOB(EJobID CHAR(2) 
                                                        CONSTRAINT EMPJOB_EJobID_pk PRIMARY KEY,
                                                        EJobDesc CHAR(10) NOT NULL,EJobMinPay NUMBER(4,2) DEFAULT 0.0,
                                                        EJobMaxPay NUMBER(4,2) DEFAULT 0.0)");
                ejobcmd.ExecuteNonQuery();
                SQLiteCommand empcmd = db.CreateCommand(@"CREATE TABLE tblEMPLOYEE(EmpID CHAR(3) 
                                                       CONSTRAINT EMPLOYEE_EmpID_pk PRIMARY KEY,
                                                       EmpName VARCHAR(25) NOT NULL,EmpHireDate DATE,
                                                       EmpHourPay NUMBER(5,2) DEFAULT 0.0,EJobID CHAR(2) 
                                                       CONSTRAINT EMPLOYEE_EJobID_fk REFERENCES tblEMPJOB(EJobID))");
                empcmd.ExecuteNonQuery();
                SQLiteCommand seatingcmd = db.CreateCommand(@"CREATE TABLE tblSEATING(TableID CHAR(2) 
                                                            CONSTRAINT SEATING_TableID_pk PRIMARY KEY,
                                                            TableLocation char(10) NOT NULL,
                                                            TableSize SMALLINT NOT NULL)");
                seatingcmd.ExecuteNonQuery();
                SQLiteCommand billcmd = db.CreateCommand(@"CREATE TABLE tblCUSTBILL(BillNum CHAR(5) 
                                                    CONSTRAINT CUSTBILL_BillNum_pk PRIMARY KEYTableID CHAR(2) 
                                                    CONSTRAINT CUSTBILL_TableID_fk REFERENCES tblSEATING(TableID),
                                                    EmpID CHAR(3) CONSTRAINT CUSTBILL_EmpID_fk REFERENCES tblEMPLOYEE(EmpID),
                                                    BillStartTime TIME NOT NULL,BillEndTime TIME NOT NULL)");
                billcmd.ExecuteNonQuery();
                SQLiteCommand menucmd = db.CreateCommand(@"CREATE TABLE tblMENUITEMS(MINum CHAR(4) CONSTRAINT MENUITEMS_MINum_pk PRIMARY KEY,
                                                    MIDesc VARCHAR(100) NOT NULL,MIPrice NUMBER(5,2) DEFAULT 0.0,
                                                    MIType CHAR(10) NOT NULL);");
                menucmd.ExecuteNonQuery();
                SQLiteCommand itemsordcmd = db.CreateCommand(@"CREATE TABLE tblITEMSORDERED(BillNum CHAR(5) CONSTRAINT ITEMSORDERED_BillNum_fk REFERENCES tblCUSTBILL(BillNum),
                                                    MINum CHAR(4) CONSTRAINT ITEMSORDERED_MINum_fk REFERENCES tblMENUITEMS(MINum),
                                                    QtyOrdered SMALLINT,
                                                    CONSTRAINT ITEMSORDERED_BillNum_MINum_pk PRIMARY KEY (BillNum, MINum))");
                itemsordcmd.ExecuteNonQuery();
            }
            catch (SQLiteException sqlexcep)
            {
                MessageBox.Show("Could not create table " + sqlexcep.Message);
            }
        }
    }
}
